﻿CREATE TABLE [dbo].[ConturiCoresp] (
    [ContDebit]  [dbo].[TipCont] NOT NULL,
    [ContCredit] [dbo].[TipCont] NOT NULL,
    [IdJurnal]   INT             NOT NULL,
    [CodUnitate] SMALLINT        CONSTRAINT [DF_ConturiCoresp_CodUnitate] DEFAULT ((0)) NOT NULL,
    [Tip]        NCHAR (10)      NULL,
    CONSTRAINT [PK_ConturiCoresp] PRIMARY KEY CLUSTERED ([ContDebit] ASC, [ContCredit] ASC, [IdJurnal] ASC, [CodUnitate] ASC),
    CONSTRAINT [FK_ConturiCoresp_Conturi] FOREIGN KEY ([ContDebit], [CodUnitate]) REFERENCES [dbo].[Conturi] ([Cont], [CodUnitate]),
    CONSTRAINT [FK_ConturiCoresp_Conturi1] FOREIGN KEY ([ContCredit], [CodUnitate]) REFERENCES [dbo].[Conturi] ([Cont], [CodUnitate]),
    CONSTRAINT [FK_ConturiCoresp_Jurnale] FOREIGN KEY ([IdJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
    CONSTRAINT [FK_ConturiCoresp_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
);


GO
-- =============================================
-- Author:		Dorin Popa
-- Create date: 05.12.2008
-- Description:	Verifica conturile
-- =============================================
CREATE TRIGGER [dbo].[ConturiCoresp_T1] 
   ON  [dbo].[ConturiCoresp]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @mesaj varchar(5000)
	DECLARE @contUpdate TipCont
	DECLARE @eContTerminal bit

    IF UPDATE(ContDebit)
	BEGIN
		SELECT @eContTerminal = Terminal,@contUpdate=[ContDebit] FROM [Conturi] c 
		JOIN INSERTED i ON c.[Cont] = i.ContDebit AND c.[CodUnitate]=i.[CodUnitate]
		IF @eContTerminal=0
			GOTO Error	
	END
	IF UPDATE(ContCredit)
	BEGIN		
		SELECT @eContTerminal = Terminal,@contUpdate=[ContCredit] FROM [Conturi] c 
		JOIN INSERTED i ON c.[Cont] = i.ContCredit AND c.[CodUnitate]=i.[CodUnitate]
		IF @eContTerminal=0
			GOTO Error
	END
	GOTO Gata
	Error:
		SET @mesaj = 'Contul ' + @contUpdate + ' nu poate fi folosit in conturi corespondente!'
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Gata:

END